from django.db import connections
from Dashboard.sqls import *
import numpy as np
from Dashboard.constant import *
from illness_protection.models import *


def sql_execute_fetchall(sql):
    cursor = connections['dashboard'].cursor()
    cursor.execute(sql)
    return cursor.fetchall()


def sql_execute_fetchone(sql):
    cursor = connections['dashboard'].cursor()
    cursor.execute(sql)
    return cursor.fetchone()


# 连续5天身体不适人数统计
def fun6():
    res = sql_execute_fetchall(sqlList.SQL_6)
    unhealthy, fever, caugh, shortness_of_breath, other = 0, 0, 0, 0, 0
    for submit_time, health_situaiton, num in res:
        if health_situaiton == 1:
            good = num
        elif health_situaiton == 2:
            fever = num
        elif health_situaiton == 3:
            caugh = num
        elif health_situaiton == 4:
            shortness_of_breath = num
        elif health_situaiton == 5:
            other = num
    unhealthy = fever + caugh + shortness_of_breath + other
    return unhealthy, fever, caugh, shortness_of_breath, other


# 各级风险人数
def func7():
    res = sql_execute_fetchall(sqlList.SQL_7)
    rank_0, rank_1, rank_2 = 0, 0, 0
    for rank, num in res:
        if int(rank) == 0:
            rank_0 = num
        elif int(rank) == 1:
            rank_1 = num
        elif int(rank) == 2:
            rank_2 = num

    return {
        'rank_0': rank_0,
        'rank_1': rank_1,
        'rank_2': rank_2
    }


# 平均日填报率
def fun8():
    res = sql_execute_fetchall(sqlList.REPORTING_RATE_IN_RECENT_5_DAYS)
    report_rates = []
    for submit_time, report_num, unreport_num, report_rate, unreport_rate in res:
        report_rates.append(report_rate)
    return round(np.mean(report_rates), 2)


def fun10():
    res = sql_execute_fetchall(sqlList.get_stu_tea_in_ill)
    return res


def top_cities():
    res = sql_execute_fetchall(sqlList.SQL_TOP_CITIES)
    return res


def ill_count():
    ill_area_stu_count, ill_area_tea_count = sql_execute_fetchone(sqlList.get_ill_count)
    return ill_area_stu_count, ill_area_tea_count


# 回莞时间方式统计
def back_dg_cal():
    res = sql_execute_fetchall(sqlList.SQL_PLAN_BACK_DG)
    return res


def back_dg_cal_topt():
    topt = []
    res = sql_execute_fetchall(sqlList.get_plan_back_dg_count_desc)
    topt.append(res[0])
    topt.append(res[1])
    topt.append(res[2])
    return topt


# 获取计划最早最晚回东莞日期
def back_dg_first_last():
    res = sql_execute_fetchall(sqlList.SQL_PLAN_BACK_DG)
    first_time_back_dg, first_transport, first_count = res[0]
    last_time_back_dg, last_transport, last_count = res[len(res)-1]
    return first_time_back_dg, last_time_back_dg


def func15():
    res = sql_execute_fetchall(sqlList.SQL_15)
    sum_num, i = 0, 0
    cities = []
    for city, num in res:
        if i < 3:
            cities.append([city, num])
        sum_num += num

    print(cities)
    return {
        'sum': sum_num,
        'rate': round(sum_num / 22149 * 1000, 2),
        'cities': cities
    }

def func15_stu():
    res = sql_execute_fetchall(sqlList.SQL_15_STU)
    sum_num, i = 0, 0
    cities = []
    for city, num in res:
        if i < 3:
            cities.append([city, num])
        sum_num += num

    print(cities)
    return {
        'sum': sum_num,
        'rate': round(sum_num / 22149 * 1000, 2),
        'cities': cities
    }

def func15_tea():
    res = sql_execute_fetchall(sqlList.SQL_15_TEA)
    sum_num, i = 0, 0
    cities = []
    for city, num in res:
        if i < 3:
            cities.append([city, num])
        sum_num += num

    print(cities)
    return {
        'sum': sum_num,
        'rate': round(sum_num / 22149 * 1000, 2),
        'cities': cities
    }

def current_city_aide(username):
    sql = "SELECT submit_time, current_country, current_province, current_city, current_district FROM daily_submit_info_new WHERE username = '{}' ORDER BY submit_time DESC LIMIT 14".format(username)
    res = sql_execute_fetchall(sql)
    important_areas = []
    important_areas_map = {}
    for item in ImportantArea.objects.filter(delete=0).all():
        important_areas.append(item.region)
        important_areas_map[item.region] = item.label
    is_ever_been_to_risk_areas = False
    is_has_been_in_guangdong = True
    is_has_been_in_dongguan = True
    n_days_in_guangdong = 0
    n_days_in_dongguan = 0
    visited_risk_areas = set()      # 造访过的疫情重点关注地区
    last_visited_risk_area = None   # 上一个造访的疫情重点关注地区
    leave_risk_area_time = None     # 离开上一个造访的疫情重点关注地区的时间
    for submit_time, country, province, city, current_district in res:
        # 是否过到疫情重点关注地区
        if country in RISK_COUNTRY:
            if not is_ever_been_to_risk_areas:
                is_ever_been_to_risk_areas = True
            visited_risk_areas.add(RISK_COUNTRY_MAP[country])
            last_visited_risk_area = country
            leave_risk_area_time = None     # 未离开疫区
        if current_district in important_areas:
            if not is_ever_been_to_risk_areas:
                is_ever_been_to_risk_areas = True
            visited_risk_areas.add(important_areas_map[current_district])
            last_visited_risk_area = current_district
            leave_risk_area_time = None     # 未离开疫区
        elif last_visited_risk_area and not leave_risk_area_time:
            leave_risk_area_time = submit_time

        # 是否一致在广东省
        if province != 440000 and is_has_been_in_guangdong:
            is_has_been_in_guangdong = False

        # 返回广东省的天数
        if province == 440000:
            n_days_in_guangdong += 1
        else:
            n_days_in_guangdong = 0

        # 是否一致在广东省
        if city != 440000 and is_has_been_in_dongguan:
            is_has_been_in_dongguan = False

        # 返回东莞市的天数
        if city == 441900:
            n_days_in_dongguan += 1
        else:
            n_days_in_dongguan = 0

    # 是否曾经去过疫情重点关注地区，返回广东天数，返回东莞的天数
    return is_ever_been_to_risk_areas, is_has_been_in_guangdong, n_days_in_guangdong, is_has_been_in_dongguan, \
           n_days_in_dongguan, visited_risk_areas, leave_risk_area_time


def health_aide(username):
    sql = "SELECT get_14_health_condition('{}')".format(username)
    health_condition = sql_execute_fetchone(sql)[0]
    return health_condition


# 计算各省份师生人数
def func16():
    today = datetime.today().strftime('%Y-%m-%d')
    yesterday = (datetime.today() - timedelta(days=1)).strftime('%Y-%m-%d')
    five_days_ago = (datetime.today() - timedelta(days=5)).strftime('%Y-%m-%d')
    an_week_ago = (datetime.today() - timedelta(days=7)).strftime('%Y-%m-%d')
    sql = '''
SELECT
get_province_name(daily_submit_info_new.current_province),
daily_submit_info_new.current_province,
count(*)
FROM
daily_submit_info_new
WHERE submit_time='{yesterday}' and current_province is not null
GROUP BY daily_submit_info_new.current_province
    '''.format(yesterday=yesterday)

    sql_2 = '''
SELECT
province_city_new.name,
count(*)
FROM
daily_submit_info_new
LEFT JOIN province_city_new ON province_city_new.id = daily_submit_info_new.current_city
WHERE submit_time='{yesterday}' and current_province = {province_id}
GROUP BY daily_submit_info_new.current_city'''
    china, provinces = [], {}
    for province_name, province_id, value in sql_execute_fetchall(sql):
        china.append({'name': province_name, 'value': value})
        provinces[province_name] = []
        # print(sql_2.format(yesterday=yesterday, province_id=province_id))
        for city_name, city_value in sql_execute_fetchall(sql_2.format(yesterday=yesterday, province_id=province_id)):
            provinces[province_name].append({'name': city_name, 'value': city_value})

    return china, provinces


# 健康码统计
def healthy_status_total_tea():
    green_tea, yellow_tea, red_tea = sql_execute_fetchone(sqlList.healthy_status_total_tea)
    return green_tea, yellow_tea, red_tea

def healthy_status_total_stu():
    green_stu, yellow_stu, red_stu = sql_execute_fetchone(sqlList.healthy_status_total_stu)
    return green_stu, yellow_stu, red_stu

# 管控区域人员统计
def healthy_controller_area_total_tea():
    in_controllerd_area1_tea, in_controllerd_area2_tea = sql_execute_fetchone(sqlList.healthy_controller_area_total_tea)
    return in_controllerd_area1_tea, in_controllerd_area2_tea

def healthy_controller_area_total_stu():
    in_controllerd_area1_stu, in_controllerd_area2_stu = sql_execute_fetchone(sqlList.healthy_controller_area_total_stu)
    return in_controllerd_area1_stu, in_controllerd_area2_stu

# 疫苗接种统计
def healthy_vaccination_total_tea():
    ym_two_tea, ym_one_tea, ym_no_normal_tea, ym_no_error_tea = sql_execute_fetchone(sqlList.healthy_vaccination_total_tea)
    return ym_two_tea, ym_one_tea, ym_no_normal_tea, ym_no_error_tea

def healthy_vaccination_total_stu():
    ym_two_stu, ym_one_stu, ym_no_normal_stu, ym_no_error_stu = sql_execute_fetchone(sqlList.healthy_vaccination_total_stu)
    return ym_two_stu, ym_one_stu, ym_no_normal_stu, ym_no_error_stu